package thitracnghiem;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
/**
 *
 * @author Odotube
 */
public class KetQuaThi {

    String maSV;
    String maMonThi;
    int lanThi;
    String dsCauHoi;
    String dsCauTraLoi;
    String dsDapAn;
    float diem;

    public KetQuaThi() {
    }

    public KetQuaThi(String maSV, String maMonHoc, int lanThi, String dsCauHoi, String dsCauTraloi, String dsDapAn, float diem) {
        this.maSV = maSV;
        this.maMonThi = maMonHoc;
        this.lanThi = lanThi;
        this.dsCauHoi = dsCauHoi;
        this.dsCauTraLoi=dsCauTraloi;
        this.diem = diem;
        this.dsDapAn = dsDapAn;
    }
    static Connection conn;
    static Statement stm;

    public static void openDatabase() throws SQLException {
        conn = DriverManager.getConnection("jdbc:odbc:ttn");
        stm = conn.createStatement();
    }

    public static void closeDatabase() throws SQLException {
        stm.close();
        conn.close();
    }

    public void setMaSV(String maSV) {
        this.maSV = maSV;
    }

    public String getMaSV() {
        return this.maSV;
    }

    public void setMaMonThi(String maMonHoc) {
        this.maMonThi = maMonHoc;
    }

    public String getMaMonThi() {
        return this.maMonThi;
    }

    public void setLanThi(int lanThi) {
        this.lanThi = lanThi;
    }

    public int getLanThi() {
        return this.lanThi;
    }

    public void setDSCauHoi(String dsCauHoi) {
        this.dsCauHoi = dsCauHoi;
    }

    public String getDSCauHoi() {
        return this.dsCauHoi;
    }

    public void setDSCauTraLoi(String dsCauTraLoi) {
        this.dsCauTraLoi = dsCauTraLoi;
    }

    public String getDSCauTraloi() {
        return this.dsCauTraLoi;
    }

    public float getDiem() {
        return this.diem;
    }

    public void ghiKetQua() {
        try {
            String query = "update KetQuaThi set dsmacautraloi='" + dsCauTraLoi + "', diem = " + diem
                    + " where masv='" + maSV + "' and mamonthi='" + maMonThi + "' and lanthi=" + lanThi;
            openDatabase();
            stm.executeUpdate(query);
            closeDatabase();

        } catch (SQLException ex) {
            Logger.getLogger(KetQuaThi.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static StringBuilder getBaiThi(String maSV, String maMonThi, int lanThi) throws SQLException {
        StringBuilder ms = new StringBuilder();
        String sql = "select * from SinhVien where masv='" + maSV + "'";
        ResultSet rs1 = stm.executeQuery(sql);
        sql = "select dsmacauhoi, dsmacautraloi, dsdapan from KetQuaThi where masv='" + maSV + "' and mamonthi='"
                + maMonThi + "' and lanthi=" + lanThi;

        if (!rs1.next()) {
            return null;
        }
        ms.append("MSVV: ").append(rs1.getString(1)).append("<br>");
        ms.append("Ho ten: ").append(rs1.getString(2)).append("<br>");
        ms.append("Lop: ").append(rs1.getString(4)).append("<br>");
        ms.append("<table border='2' width='100%'><th>Cau hoi<th>Cau tra loi<th>Dap an");
        ResultSet rs2 = stm.executeQuery(sql);
        if (!rs2.next()) {
            return null;
        }

        String[] strings1, strings2, strings3;
        String temp1 = rs2.getString(1);
        String temp2 = rs2.getString(2);
        String temp3 = rs2.getString(3);
        if (temp1 != null && temp2 != null && temp3 != null) {
            strings1 = temp1.split(";");
            strings2 = temp2.split(";");
            strings3 = temp3.split(";");
            for (int i = 0; i < strings1.length; i++) {
                sql = "select cauhoi from CauHoi where macauhoi='" + strings1[i]+"'";
                rs2 = stm.executeQuery(sql);
                if (rs2.next()) {
                    ms.append("<tr><td width=80%>").append(rs2.getString(1)).append("</td>")
                            .append("<td width=10%>").append(strings2[i]).append("</td>")
                            .append("<td width=10%>" + strings3[i]).append("</td></tr><br>");
                }
            }
        }
        return ms;
    }

    public static float getDiemTheoMML(String maSV, String maMonThi, int lanThi) throws SQLException {
        String sql = "select * from KetQuaThi where masv='" + maSV + "' and mamonthi='" + maMonThi + "' and lanthi=" + lanThi;
        KetQuaThi.openDatabase();
        ResultSet rs = stm.executeQuery(sql);
        if (rs.next()) {
            return rs.getFloat("diem");
        }
        KetQuaThi.closeDatabase();
        return (float) -1.0;

    }
    
   public static void main(String[] args ) throws SQLException{
       KetQuaThi.openDatabase();
       KetQuaThi.getBaiThi("1", "TCC", 1);
       KetQuaThi.closeDatabase();
   }
}
